﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CodeRepositoryConsole.ORM.ADO.NET
{
    public class DataAdaper
    {

        /**
         *  SqlDataAdapter/DataSet
         *  Lets you close the connection as soon it's done loading data, and may even close it for you automatically
         *  All of the results are available in memory
         *  You can iterate over it as many times as you need, or even look up a specific record by index
         *  Has some built-in faculties for updating back to the database
         *  At the cost of:
         *  Much higher memory use
         *  You wait until all the data is loaded before using any of it
         */

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        /// <see cref="https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataadapter-datatable-and-datacolumn-mappings"/>
        public DataTable GetDatatable2()
        {
            SqlConnection cn = new SqlConnection("Server=.;Database=AdventureWorks2014;User Id=sa;Password=123456");
            string queryString = "select top 100 * from  [Person].[Person];select top 100 * from [Person].[PersonPhone];";
            SqlDataAdapter adapter = new SqlDataAdapter(queryString, cn);

            DataTableMapping mapping =
                adapter.TableMappings.Add("Table", "NorthwindCustomers");
            mapping.ColumnMappings.Add("Title", "Title123");

            var dt = new DataTable();
            adapter.Fill(dt);

            DataSet customer = new DataSet();
            adapter.Fill(customer);
            adapter.Fill(customer); // append rows again
            cn.Close();

            return customer.Tables[0];
        }

        public DataTable GetDatatablePage()
        {
            SqlConnection cn = new SqlConnection("Server=.;Database=AdventureWorks2014;User Id=sa;Password=lh123456");
            string queryString = "select top 100 * from  [Person].[Person];";
            SqlDataAdapter adapter = new SqlDataAdapter(queryString, cn);

            var dt = new DataTable();            
            DataSet customer = new DataSet();
            adapter.Fill(0, 5, dt);
   
            //adapter.Update()

            return dt;
        }

    }
}
